using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.Data.Extensions;
using System;
using Devart.Data.PostgreSql;

namespace MvcCms.Data.Code
{
    public class EntityPortalRepository : IPortalRepository
    {
        #region Constructors

        private readonly IEntityAccountRepository _account;
        private readonly MvcCmsEntities _entities;
        private readonly IEntitiesProvider _entitiesProvider;

        private EntityPortalRepository(IEntitiesProvider entitiesProvider)
        {
            _entities = entitiesProvider.GetEntityContext();
            _entitiesProvider = entitiesProvider;
        }

        public EntityPortalRepository(IEntitiesProvider entitiesProvider, IEntityAccountRepository account)
        {
            _entities = entitiesProvider.GetEntityContext();
            _entitiesProvider = entitiesProvider;
            _account = account;

        }
        #endregion



        #region Portal methods

        public Portal CreatePortal(Portal portalToCreate)
        {
            portalToCreate.PortalAdmin = _account.GetUser(portalToCreate.PortalAdmin.UserID);

            _entities.PortalSet.AddObject(portalToCreate);
            _entities.SaveChanges();
            return portalToCreate;
        }

        public Portal GetPortal(int id)
        {
            return (from s in _entities.PortalSet.Include("DefaultLanguage")
                    where s.PortalID == id
                    select s).FirstOrDefault();
        }

        public Portal GetPortal()
        {
            return (from s in _entities.PortalSet.Include("DefaultLanguage")
                    where s.Name.ToLower() == "default"
                    select s).FirstOrDefault();
        }

        public Portal GetPortal(string urlAuthority)
        {
            return (from s in _entities.PortalSet
                    where s.URLAuthority.ToLower() == urlAuthority.ToLower()
                    select s).FirstOrDefault();
        }
        public Portal GetPortalByName(string portalName)
        {
            return (from s in _entities.PortalSet
                    where s.Name.ToLower() == portalName.ToLower()
                    select s).FirstOrDefault();
        }
        public IEnumerable<Portal> ListPortals()
        {
            return (from c in _entities.PortalSet
                       .Include("Areas")
                       .Include("DefaultLanguage")
                       .Include("PortalResources")
                    orderby c.Name
                    select c).ToList();
        }

        public IEnumerable<PortalResourceType> ListPortalResourceTypes()
        {
            return (from c in _entities.PortalResourceTypeSet
                    orderby c.Name
                    select c).ToList();
        }

        public Portal EditPortal(Portal portalToEdit)
        {
            Portal originalPortal = GetPortal(portalToEdit.PortalID);

            // Save changes
            _entities.PortalSet.ApplyCurrentValues(portalToEdit);
            _entities.SaveChanges();
            return portalToEdit;
        }

        public void DeletePortal(Portal portalToDelete)
        {
            DbCommand command = _entities.CreateStoreCommand("delete_Portal",
                 CommandType.StoredProcedure,
                 new PgSqlParameter("_PortalID", portalToDelete.PortalID)
                );
            using (command.Connection.CreateConnectionScope())
            {
                command.ExecuteReader();
                var portals = ListPortals();
                _entitiesProvider.Refresh(_entities, RefreshMode.StoreWins, portals);
            }
        }

        #endregion

        #region Portal Resource methods

        public int CreatePortalResource(PortalResource portalresourceToCreate)
        {
            DbCommand command = _entities.CreateStoreCommand("insert_PortalResource",
                CommandType.StoredProcedure,
                new PgSqlParameter("_ResourceName", portalresourceToCreate.ResourceName),
                new PgSqlParameter("_Body", portalresourceToCreate.Body),
                new PgSqlParameter("_PortalID", portalresourceToCreate.Portal.PortalID),
                new PgSqlParameter("_EditedBy", portalresourceToCreate.PortalAdmin.UserID),
                new PgSqlParameter("_ResourceTypeID", portalresourceToCreate.PortalResourceType.PortalResourceTypeID)
                );
            using (command.Connection.CreateConnectionScope())
            {
                using (DbDataReader reader = command.ExecuteReader())
                {
                    reader.Read();
                    object resourceid = reader.GetValue(0);
                    return int.Parse(resourceid.ToString());
                }
            }
        }

        public PortalResource GetPortalResource(int id)
        {
            return (from s in _entities.PortalResourceSet
                        .Include("PortalResourceType")
                        .Include("Portal")
                    where s.PortalResourceID == id
                    select s).FirstOrDefault();
        }

        public PortalResourceType GetPortalResourceType(int id)
        {
            return (from s in _entities.PortalResourceTypeSet
                    where s.PortalResourceTypeID == id
                    select s).FirstOrDefault();
        }

        public PortalResource GetPortalResource(int portalid, int typeid)
        {
            return (from s in _entities.PortalResourceSet
                        .Include("PortalResourceType")
                        .Include("Portal")
                    where s.PortalResourceType.PortalResourceTypeID == typeid &&
                          s.Portal.PortalID == portalid
                    select s).FirstOrDefault();
        }

        public PortalResource GetPortalResource(int portalid, string typename)
        {
            return (from s in _entities.PortalResourceSet
                    where s.PortalResourceType.Name.ToLower() == typename.ToLower() &&
                          s.Portal.PortalID == portalid
                    select s).FirstOrDefault();
        }

        public IEnumerable<PortalResource> ListPortalResources(Portal portal)
        {
            return (from c in _entities.PortalResourceSet
                    where c.Portal.PortalID == portal.PortalID
                    orderby c.EditDate
                    select c).ToList();
        }

        public PortalResource EditPortalResource(PortalResource portalresourceToEdit)
        {
            DbCommand command = _entities.CreateStoreCommand("update_PortalResource",
                     CommandType.StoredProcedure,
                     new PgSqlParameter("_ResourceName", portalresourceToEdit.ResourceName),
                     new PgSqlParameter("_Body", portalresourceToEdit.Body),
                     new PgSqlParameter("_EditedBy", portalresourceToEdit.PortalAdmin.UserID),
                     new PgSqlParameter("_PortalResourceID", portalresourceToEdit.PortalResourceID)
                );
            using (command.Connection.CreateConnectionScope())
            {
                command.ExecuteReader();
                _entitiesProvider.Refresh(_entities, RefreshMode.StoreWins, _entities.PortalResourceSet);
                return portalresourceToEdit;
            }
        }
        public FeedBackItem CreateFeedBackItem(FeedBackItem feedBackItemToCreate)
        {
            _entities.FeedBackItemSet.AddObject(feedBackItemToCreate);
            _entities.SaveChanges();
            return feedBackItemToCreate;
        }
        #endregion

        #region Email Templates
        public EmailTemplate CreateEmailTemplate(EmailTemplate emailTemplateToCreate)
        {
            _entities.EmailTemplateSet.AddObject(emailTemplateToCreate);
            _entities.SaveChanges();
            return emailTemplateToCreate;
        }
        private EmailTemplate GetEmailTemplate(int emailTemplateId)
        {
            return (from s in _entities.EmailTemplateSet
                    where s.EmailTemplateID == emailTemplateId
                    select s).FirstOrDefault();
        }
        public EmailTemplate EditEmailTemplate(EmailTemplate emailTemplateToEdit)
        {
            var originalTemplate = GetEmailTemplate(emailTemplateToEdit.EmailTemplateID);

            // Save changes
            _entities.EmailTemplateSet.ApplyCurrentValues(emailTemplateToEdit);
            _entities.SaveChanges();
            return emailTemplateToEdit;
        }
        public IEnumerable<EmailTemplate> ListEmailTemplates(int portalId)
        {
            return (from c in _entities.EmailTemplateSet
                    where c.Portal.PortalID == portalId
                    select c).ToList();
        }


        #endregion

    }
}